import requests
from bs4 import BeautifulSoup
import re
import sqlite3

def Download_Symposium(sqlcur, symposiumLabel):
    urlprefix = 'https://iafastro.directory'
    urlroot = urlprefix + f'/iac/browse/IAC-24/C1/{symposiumLabel}/'
    print(f"waiting for {urlroot}")
    response = requests.get(urlroot)
    response.raise_for_status()
    soup = BeautifulSoup(response.text, 'html.parser')
    papersTable = soup.select('#childs')
    tr_tags = papersTable[0].find_all('tr')

    for n1, paperRow in enumerate(tr_tags):
        if n1 == 0:
            continue
        try:
            aSoup = paperRow.find('a')
            infoSoup = paperRow.find_all('td')
            paperAbsUrl = urlprefix + aSoup['href']
            paperName = aSoup.text
            paperName = paperName.replace("'", "_")
            paperAuthor = infoSoup[5].text
            paperCountry = infoSoup[7].text
            print(f"waiting for {paperAbsUrl}")
            response = requests.get(paperAbsUrl)
            response.raise_for_status()
            paperSoup = BeautifulSoup(response.text, 'html.parser')
            aSoup = paperSoup.select('body > article > dl:nth-child(3) > dd:nth-child(14) > p > a')
            paperUrl = aSoup[0]['href']
            paperUrl = paperAbsUrl.rstrip('summary/') + re.split('\?.+', paperUrl)[0]
            print(paperName)
            sqlstr = "INSERT INTO urls VALUES ("
            sqlstr += f"'{symposiumLabel}_{n1}', "
            sqlstr += f"'{paperName}', "
            sqlstr += f"'{paperAuthor}', "
            sqlstr += f"'{paperCountry}', "
            sqlstr += f"'{paperUrl}')"
            sqlcur.execute(sqlstr)
        except Exception as e:
            print(e)
            continue

if __name__ == '__main__':
    conn = sqlite3.connect('abstractUrl.db')
    cursor = conn.cursor()
    cursor.execute("DROP TABLE IF EXISTS urls")
    cursor.execute('''
        CREATE TABLE urls (
        paperid TEXT PRIMARY KEY NOT NULL,
        title TEXT,
        author TEXT,
        country TEXT,
        url TEXT
    );''')
    for n1 in range(1, 10):
        Download_Symposium(cursor, n1)
        conn.commit()
    conn.close()
